package com.epoint.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.druid.util.StringUtils;
import com.epoint.entity.BorrowInfo;
import com.epoint.utils.JDBCUtils;

/**
 * @Description 借阅信息列表
 * @author ylc
 */
public class BorrowInfoDao {

	/**
	 * 获取借阅信息列表
	 * 
	 * @param pageIndex
	 * @param pageSize
	 * @param sortField
	 * @param sortOrder
	 * @return
	 */
	public List<BorrowInfo> getBorrowList(int pageIndex, int pageSize, String sortField, String sortOrder) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		List<BorrowInfo> borrowList = new ArrayList<>();
		List<Object> params = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("select a.*, b.bookname from borrowinfo a inner join bookinfo b on a.bookid = b.bookid where 1=1");

		// 排序条件
		if (!StringUtils.isEmpty(sortOrder) && !StringUtils.isEmpty(sortField)) {
			sb.append(" order by " + sortField + " " + sortOrder);
		}
		// 分页条件
		if (-1 != pageIndex && -1 != pageSize) {
			sb.append(" limit ?,?");
			params.add(pageIndex * pageSize);
			params.add(pageSize);
		}
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = sb.toString();
			pstmt = conn.prepareStatement(sql);

			// 给?赋值
			int sizeCount = params.size();
			for (int i = 0; i < sizeCount; i++) {
				if ((sizeCount - i) <= 2) {
					pstmt.setInt(i + 1, Integer.parseInt(params.get(i).toString()));
				} else {
					pstmt.setString(i + 1, params.get(i).toString());
				}
			}
			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				BorrowInfo borrowInfo = new BorrowInfo();
				borrowInfo.setBookName(rs.getString("bookname"));
				borrowInfo.setBookId(rs.getString("bookid"));
				borrowInfo.setBorrower(rs.getString("borrower"));
				borrowInfo.setBorrowId(rs.getString("borrowid"));
				borrowInfo.setPhone(rs.getString("phone"));
				borrowInfo.setBorrowTime(rs.getTimestamp("borrowtime"));
				borrowInfo.setReturnTime(rs.getTimestamp("returntime"));

				borrowList.add(borrowInfo);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}

		return borrowList;
	}

	/**
	 * 获取图书列表总数
	 * 
	 * @return
	 */
	public int getBorrowListCount() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count = 0;

		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select count(1) from borrowinfo where 1=1";
			pstmt = conn.prepareStatement(sql);

			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return count;
	}

	/**
	 * 获取最大的BorrowId
	 * 
	 * @return
	 */
	public String getMaxBorrowId() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String res = "";
		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select borrowid from borrowinfo order by borrowid desc limit 1";
			pstmt = conn.prepareStatement(sql);

			// 执行查询，获取结果集
			rs = pstmt.executeQuery();
			while (rs.next()) {
				res = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return res;
	}

	/**
	 * 新增借阅信息
	 * 
	 * @param borrowInfo
	 * @return
	 */
	public int addBorrowInfo(BorrowInfo borrowInfo) {
		Connection conn = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs = null;
		int res = 0;

		try {
			conn = JDBCUtils.getConnection();

			// 关闭自动提交，手动提交事务
			conn.setAutoCommit(false);
			// 添加借阅信息
			String insertSql = "INSERT INTO bookmanager.borrowinfo (borrowid, bookid, borrower, phone, borrowtime,returntime) VALUES(?, ?, ?, ?, ?, ?);";
			pstmt1 = conn.prepareStatement(insertSql);
			pstmt1.setString(1, borrowInfo.getBorrowId());
			pstmt1.setString(2, borrowInfo.getBookId());
			pstmt1.setString(3, borrowInfo.getBorrower());
			pstmt1.setString(4, borrowInfo.getPhone());
			Timestamp date = new Timestamp(borrowInfo.getBorrowTime().getTime());
			pstmt1.setTimestamp(5, date);

			if (null != borrowInfo.getReturnTime()) {
				pstmt1.setTimestamp(6, new Timestamp(borrowInfo.getReturnTime().getTime()));
			} else {
				pstmt1.setDate(6, null);
			}
			res = pstmt1.executeUpdate();

			// 修改图书剩余数量
			String updateSql = "update bookInfo set remain=remain-1 where bookid=?";
			pstmt2 = conn.prepareStatement(updateSql);
			pstmt2.setString(1, borrowInfo.getBookId());

			res = pstmt2.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt1, conn);
			JDBCUtils.close(pstmt2);
		}
		return res;
	}

	/**
	 * 更新借阅信息
	 * 
	 * @param borrowInfo
	 * @return
	 */
	public int updateBorrowInfo(BorrowInfo borrowInfo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmtUpdate = null;
		ResultSet rs = null;
		int res = 0;
		try {
			conn = JDBCUtils.getConnection();
			// 关闭自动提交，手动提交事务
			conn.setAutoCommit(false);
			// 获取执行SQL的对象
			String sql = "UPDATE bookmanager.borrowinfo SET bookid=?, borrower=?, phone=?, borrowtime=?, returntime=? WHERE borrowid=?";
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, borrowInfo.getBookId());
			pstmt.setString(2, borrowInfo.getBorrower());
			pstmt.setString(3, borrowInfo.getPhone());
			Timestamp date = new Timestamp(borrowInfo.getBorrowTime().getTime());
			pstmt.setTimestamp(4, date);
			pstmt.setTimestamp(5, new Timestamp(borrowInfo.getReturnTime().getTime()));
			pstmt.setString(6, borrowInfo.getBorrowId());
			res = pstmt.executeUpdate();

			// 修改图书剩余数量
			String updateSql = "update bookInfo set remain=remain+1 where bookid=?";
			pstmtUpdate = conn.prepareStatement(updateSql);
			pstmtUpdate.setString(1, borrowInfo.getBookId());

			res = pstmtUpdate.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
			JDBCUtils.close(pstmtUpdate);
		}
		return res;
	}

	/**
	 * 删除借阅信息
	 * 
	 * @param borrowId
	 * @return
	 */
	public int deleteBorrowInfo(String borrowId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int res = 0;

		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "DELETE FROM borrowinfo WHERE borrowid=?";
			pstmt = conn.prepareStatement(sql);
			// 给?赋值
			pstmt.setString(1, borrowId);

			// 执行查询，获取结果
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(pstmt, conn);
		}
		return res;
	}

	/**
	 * 判断某本图书是否被借阅
	 * 
	 * @param bookId
	 * @return 被借阅的数量
	 */
	public int isBorrowByBookId(String bookId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int res = 0;

		try {
			conn = JDBCUtils.getConnection();
			// 获取执行SQL的对象
			String sql = "select count(1) from borrowinfo where bookid = ? and ifnull(returntime,'') = ''";
			pstmt = conn.prepareStatement(sql);
			// 给?赋值
			pstmt.setString(1, bookId);

			// 执行查询，获取结果
			rs = pstmt.executeQuery();
			while (rs.next()) {
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(pstmt, conn);
		}
		return res;
	}

}
